package bit_poi;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.util.Date;

/**
 * @author yuisama
 * @date 2023/01/02 11:10
 **/
public class POITest {
    public static void main(String[] args) {
        // 获取桌面路径
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String deskTop = fsv.getHomeDirectory().getPath();
        String filePath = deskTop + "/template.xls";
        // 在桌面生成表格文件
        File file = new File(filePath);
        try {
            // 获取表格文件输入流
            OutputStream os = new FileOutputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("test sheet");
            HSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("id");
            row.createCell(1).setCellValue("订单号");
            row.createCell(2).setCellValue("下单时间");
            row.createCell(3).setCellValue("个数");
            row.createCell(4).setCellValue("单价");
            row.createCell(5).setCellValue("订单金额");
            row.setHeightInPoints(30); // 设置行的高度

            HSSFRow row1 = sheet.createRow(1);
            row1.createCell(0).setCellValue("1");
            row1.createCell(1).setCellValue("NO00001");

            // 日期格式化
            HSSFCellStyle cellStyle2 = workbook.createCellStyle();
            HSSFCreationHelper creationHelper = workbook.getCreationHelper();
            cellStyle2.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
            sheet.setColumnWidth(2, 20 * 256); // 设置列的宽度

            HSSFCell cell2 = row1.createCell(2);
            cell2.setCellStyle(cellStyle2);
            cell2.setCellValue(new Date());

            row1.createCell(3).setCellValue(2);


            // 保留两位小数
            HSSFCellStyle cellStyle3 = workbook.createCellStyle();
            cellStyle3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
            HSSFCell cell4 = row1.createCell(4);
            cell4.setCellStyle(cellStyle3);
            cell4.setCellValue(29.5);


            // 货币格式化
            HSSFCellStyle cellStyle4 = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setFontName("华文行楷");
            font.setFontHeightInPoints((short)15);
            font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
            cellStyle4.setFont(font);

            HSSFCell cell5 = row1.createCell(5);
            cell5.setCellFormula("D2*E2");  // 设置计算公式

            // 获取计算公式的值
            HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
            cell5 = e.evaluateInCell(cell5);
            System.out.println(cell5.getNumericCellValue());


            workbook.setActiveSheet(0);
            workbook.write(os);
            os.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}
